/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package categoryBean;

import DBConnection.ATeamObject;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import javax.ejb.*;
import javax.jms.*;
import javax.sql.*;
import javax.naming.*;
/**
 * Bean for category from database
 * @author Konstantin
 */
public class CategoryBean implements EntityBean{
    
    private EntityContext entityContext;
    private java.sql.Connection connection;
    private DataSource dataSource;
    
    private MessageProducer publisher;
    private ConnectionFactory connectionFactory;
    private javax.jms.Connection factoryConnection;
    private Queue messageQueue;
    private Session messageSession;
    
    private Integer objectId;
    private String name;
    private String objectDescription;
    private Integer parentId;
    private String createDate;
    private String lastModifyDate;
    
    public CategoryBean(){
        
    }
    
    /**
     * returns value of <i>objectId</i>
     * @return objectId
     */
    public Integer getObjectId() {
        return objectId;
    }

    /**
     * Sets value of designated parameter in <i>name</i>
     * @param ato The value from ato to be set in <i>name</i>
     */
    public void setName(ATeamObject ato) {
        
        String messageDescription = "Name is changed from " +
                    this.name + " to " + name + ".";
        
        name = ato.getName();
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * returns value of <i>name</i>
     * @return name
     */
    public String getName() {
        return name;
    }
    
    /**
     * returns value of <i>createDate</i>
     * @return createDate
     */
    public String getCreateDate(){
        return createDate;
    }
    
    /**
     * returns value of <i>lastModifyDate</i>
     * @return lastModifyDate
     */
    public String getLastModifyDate(){
        return lastModifyDate;
    }
    
    /**
     * Sets value of designated parameter in <i>objectDescription</i>
     * @param ato The value from ato to be set in <i>objectDescription</i>
     */
    public void setObjectDescription(ATeamObject ato) {
        
        String messageDescription = "Description is changed.";
        
        objectDescription = ato.getObjectDescription();
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * returns value of <i>objectDescription</i>
     * @return objectDescription
     */
    public String getObjectDescription() {
        return objectDescription;
    }
    
    /**
     * Sets value of designated parameter in <i>parentId</i>
     * @param ato The value from ato to be set in <i>parentId</i>
     * @throws RemoteException
     * @see RemoteException
     */
    public void setParentId(ATeamObject ato) {
        
        String messageDescription = "ParentId is changed from " +
                    this.parentId + " to " + parentId + ".";
        
        parentId = new Integer(ato.getParentId());
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * returns value of <i>parentId</i>
     * @return parentId
     */
    public Integer getParentId() {
        return parentId;
    }
    
    private void sendEditMessage(String messageDescription) {
        
        try {
            
            setPublisher();
            
            MapMessage message = messageSession.createMapMessage();
            message.setInt("ObjectId", objectId);
            message.setInt("ActionId", 2);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 3, 0);
        } catch (JMSException ex) {
            ex.printStackTrace(System.err);
            throw new EJBException(ex.getMessage());
        } finally {
            unsetPublisher();
        }
    }
    
    /**
     * Find category by id.
     * @return categoryId
     * @throws ObjectNotFoundException
     * @see ObjectNotFoundException
     */
    public Integer ejbFindByPrimaryKey(Integer primaryKey) 
            throws ObjectNotFoundException {
        
        String findByPrimaryKeyQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.ObjectId = ?";
        PreparedStatement findByPrimaryKeyQueryStat = null;
        ResultSet res = null;
        
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            findByPrimaryKeyQueryStat = 
                    connection.prepareStatement(findByPrimaryKeyQuery);
            findByPrimaryKeyQueryStat.setInt(1, primaryKey.intValue());
            res = findByPrimaryKeyQueryStat.executeQuery();
            if(res.next()){
                res.close();
                findByPrimaryKeyQueryStat.close();
                return primaryKey;
            }
            else{
                throw new ObjectNotFoundException("Category hasn't been found");
            } 
        }
        catch(SQLException sqlException){
            
            throw new EJBException(sqlException);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find categories by name.
     * @return Collection of categoryId
     */
    public Collection ejbFindByName(ATeamObject ato) {
        
        String findByNameQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.Name = ?";
            
        PreparedStatement findByNameQueryStat = null;
        
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            findByNameQueryStat =
                    connection.prepareStatement(findByNameQuery);
            findByNameQueryStat.setString(1, ato.getName());
            ResultSet res = findByNameQueryStat.executeQuery();
            Collection Keys = new ArrayList();
            if (!res.next()) {
                
                return Keys;
            }
            else{
                do{
                    Keys.add(new Integer(res.getInt("ObjectId")));
                }while(res.next());
                res.close();
                findByNameQueryStat.close();
                return Keys;
            }
            
        }
        catch(SQLException sqlException){
            
            throw new EJBException(sqlException);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find categories by objectDescription.
     * @return Collection of categoryId
     */
    public Collection ejbFindByObjectDescription(ATeamObject ato) {
        
        String findByObjectDescriptionQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.ObjectDescription = ?";
            
        PreparedStatement findByObjectDescriptionQueryStat = null;
        ResultSet res = null;
        Collection Keys = new ArrayList();
            
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            findByObjectDescriptionQueryStat = 
                    connection.prepareStatement(findByObjectDescriptionQuery);
            findByObjectDescriptionQueryStat.setString(1,
                    ato.getObjectDescription());
            res = findByObjectDescriptionQueryStat.executeQuery();
            if (!res.next()) {
                
                return Keys;
            }
            else{
                do{
                    Keys.add(new Integer(res.getInt("ObjectId")));
                }while(res.next());
                res.close();
                findByObjectDescriptionQueryStat.close();
                return Keys;
            }
            
        }
        catch(SQLException sqlException){
            
            throw new EJBException(sqlException);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find all categories in database.
     * @return Collection of categoryId
     */
    public Collection ejbFindAll() {
        
        String findAllQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.ObjectTypeId = 1";
            
        PreparedStatement findAllQueryStat = null;
        ResultSet res = null;
        Collection Keys = new ArrayList();
            
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            findAllQueryStat = 
                    connection.prepareStatement(findAllQuery);
            res = findAllQueryStat.executeQuery();
            if (!res.next()) {
                
                return Keys;
            }
            else{
                do{
                    Keys.add(new Integer(res.getInt("ObjectId")));
                }while(res.next());
                res.close();
                findAllQueryStat.close();
                return Keys;
            }
            
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException.getMessage());
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Create new category
     * @param ato Parameters of new category
     * @return goodsId
     * @throws CreateException
     * @see CreateException
     */
    public Integer ejbCreate(ATeamObject aTeamObject) throws CreateException {
        
        String insertIntoObject ="INSERT INTO Object VALUES (?,26,?,1,?)";
        
        String getNextSeq = "SELECT Objectseq.nextval FROM DUAL";
        
        String executeProcedure =
                    "BEGIN"+
                    " GETNEXTSEQ;"+
                    " END;";
        
        String insertDateIntoParams =
                    "INSERT INTO Params"
                    + " VALUES(?,?, TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'))";
        String messageDescription;
        
        PreparedStatement executeProcedureStat = null;
        MapMessage message = null;
         
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            executeProcedureStat = 
                    connection.prepareStatement(executeProcedure);
            executeProcedureStat.execute();
            
            if (aTeamObject.getObjectId() == 0) {
                executeProcedureStat = 
                        connection.prepareStatement(getNextSeq);
                ResultSet res = executeProcedureStat.executeQuery();
                res.next();
                objectId = new Integer(res.getInt(1));
                executeProcedureStat.close();
            } else {
                objectId = aTeamObject.getObjectId();
            }
            
            this.name = aTeamObject.getName();
            this.objectDescription = aTeamObject.getObjectDescription();
            this.parentId = 26;
            
            executeProcedureStat =
                    connection.prepareStatement(insertIntoObject);
            executeProcedureStat.setInt(1,objectId);
            executeProcedureStat.setString(2,aTeamObject.getName());
            executeProcedureStat.setString(3,aTeamObject.getObjectDescription());
            executeProcedureStat.executeUpdate();
            executeProcedureStat.close();
            
            
            executeProcedureStat =
                    connection.prepareStatement(insertDateIntoParams);
            executeProcedureStat.setInt(1, objectId);
            executeProcedureStat.setInt(2, 15);
            executeProcedureStat.executeUpdate();
            
            executeProcedureStat.setInt(1, objectId);
            executeProcedureStat.setInt(2, 16);
            executeProcedureStat.executeUpdate();
            
            executeProcedureStat.close();
            
            setPublisher();
            
            messageDescription = "Id = " + objectId + ", ";
            messageDescription += "name = " + name + ", ";
            messageDescription += "parentId = " + parentId + ".";
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", objectId);
            message.setInt("ActionId", 1);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 4, 0);
            
            messageDescription = "Child was created with id = " + objectId+ ".";
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", parentId);
            message.setInt("ActionId", 3);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 2, 0);
                                   
            return objectId;
        }
        catch(SQLException sqlException){
            throw new CreateException(sqlException.getMessage());
        }
        catch (JMSException ex) {
            throw new CreateException(ex.getMessage());
        }
        finally {
            
            closeConnection();
            unsetPublisher();
        }
    }
    
    public void ejbPostCreate(ATeamObject aTeamObject){
        
    }
    
    public void setEntityContext(EntityContext ctx) {
        entityContext = ctx;
        try{
            InitialContext initialContext = new InitialContext();
            
            dataSource =
                (DataSource) initialContext.lookup("java:/ATeamConnectionPool");           
            connection = dataSource.getConnection();
            
        }
        catch(NamingException namingException ){
            throw new EJBException(namingException);
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        }
    }

    public void unsetEntityContext()
            throws EJBException, RemoteException {
        entityContext = null;
        try{
            connection.close();
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        }
        finally{
            connection = null;
        }
    }

    public void ejbRemove() throws RemoveException {
        
        String delete = "DELETE FROM Object WHERE"+
                    " Object.ObjectId = ?";
        Integer primaryKey = null;
        String messageDescription = null;
        MapMessage message = null;
        
        try{
            
            primaryKey = (Integer) entityContext.getPrimaryKey();
            
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            
            PreparedStatement deleteStatement = connection.prepareStatement(
                    delete);
            
            deleteStatement.setInt(1, primaryKey.intValue());
            
            deleteStatement.executeUpdate();
            deleteStatement.close();
            
            setPublisher();
            
            messageDescription = "Child was deleted with id = " +
                    primaryKey + ".";
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", parentId);
            message.setInt("ActionId", 4);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 1, 0);
            
        }
        catch(SQLException sqlException){
            
            throw new RemoveException(sqlException.getMessage());
        }
        catch(JMSException ex) {
            
            throw new RemoveException(ex.getMessage());
        }
        finally {
            
            closeConnection();
            unsetPublisher();
        } 
    }

    public void ejbActivate() {
        
    }

    public void ejbPassivate() {
        
    }

    public void ejbLoad() {
        
        String selectFromObject = "SELECT * FROM Object WHERE"+
                    " Object.ObjectId = ?";
        
        String selectFromParams = 
                    "SELECT TO_DATE(VALUE,'DD.MM.YYYY HH24:MI:SS') AS VALUE,"
                    + " AttrId FROM Params"+
                    " WHERE Params.ObjectId = ?"+
                    " AND AttrId IN (15,16)" +
                    " ORDER BY AttrId";
        
        PreparedStatement selectStatement = null;
        Integer primaryKey = null;
        ResultSet resultSet = null;
        
        try{
            primaryKey = (Integer) entityContext.getPrimaryKey();
            
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            selectStatement = connection.prepareStatement(
                    selectFromObject);
            
            selectStatement.setInt(1, primaryKey.intValue());
            
            resultSet = selectStatement.executeQuery();
            
            if(resultSet.next()){
                objectId = new Integer(resultSet.getInt("ObjectId"));
                name = resultSet.getString("Name");
                objectDescription = resultSet.getString("ObjectDescription");
                parentId = new Integer(resultSet.getInt("ParentId"));
            } else { 
                
                selectStatement.close();
                throw new EJBException("No such category.");
            }    
            
            selectStatement = connection.prepareStatement(selectFromParams);
            
            selectStatement.setInt(1, primaryKey.intValue());
            
            resultSet = selectStatement.executeQuery();
            
            if(resultSet.next()){
                createDate = resultSet.getString("Value");
                resultSet.next();
                lastModifyDate = resultSet.getString("Value");
            }
            selectStatement.close();
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        } finally {
                
            closeConnection();
        }
    }

    public void ejbStore() {
        
        String editNameOfCategoryInObjectQuery = 
                "UPDATE Object"+ 
                " SET Object.Name = ?"+
                " WHERE Object.ObjectId = ?";
    
            String editDescriptionOfCategoryQuery =
                "UPDATE Object"+
                " SET Object.ObjectDescription = ?"+
                " WHERE Object.ObjectId = ?";
            
            String editDateOfCategoryQuery = 
                "UPDATE Params"+
                " SET Value = TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS')"+
                " WHERE ObjectId = ?"+
                " AND AttrId = 16";
            
        Integer primaryKey = null;
        PreparedStatement updateStatement = null;
        
        try{
            primaryKey = (Integer) entityContext.getPrimaryKey();
            
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            updateStatement =
                    connection.prepareStatement(editNameOfCategoryInObjectQuery);         
            updateStatement.setString(1, name);
            updateStatement.setInt(2, primaryKey.intValue());
            updateStatement.executeUpdate();
               
            updateStatement =
                    connection.prepareStatement(editDescriptionOfCategoryQuery);
            updateStatement.setString(1, objectDescription);
            updateStatement.setInt(2, primaryKey.intValue());
            updateStatement.executeUpdate();
            
            updateStatement =
                    connection.prepareStatement(editDateOfCategoryQuery);
            updateStatement.setInt(1, primaryKey.intValue());
            updateStatement.executeUpdate();
            
            updateStatement.close();
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Closes connection with database
     */
    void closeConnection() {
        try {
            if (connection != null){
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            throw new EJBException("closeConnection:" + e.getMessage());
        } finally {
            connection = null;
        }
    }
    
    /**
     * open new Publisher
     */
    private void setPublisher() {
        
        InitialContext initialContext = null;
        
        try {
            
            initialContext = new InitialContext();
            connectionFactory = (ConnectionFactory)initialContext.lookup(
                    "/XConnectionFactory");
            messageQueue = (Queue)initialContext.lookup(
                    "/queue/CategoryMessageQueue");
            factoryConnection = connectionFactory.createConnection();
            messageSession = factoryConnection.createSession(
                        false, Session.AUTO_ACKNOWLEDGE);
            publisher = messageSession.createProducer(messageQueue);
        } catch (Exception ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex);
        }
    }
    
    /**
     * close Publisher
     */
    private void unsetPublisher() {
        
        try {
            
            publisher.close();
            messageSession.close();
            factoryConnection.close();        
        } catch (JMSException ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex);
        } finally {
            
            publisher = null;
            messageSession = null;
            factoryConnection = null;
            messageQueue = null;
            connectionFactory = null;
        }
    }
}
